package com.bst.system.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONObject;
import com.bst.base.domain.BaseDbfldComb;
import com.bst.base.service.IBaseDbfldCombService;
import com.bst.base.service.IBaseMsgService;
import com.bst.common.constant.JobConstant;
import com.bst.common.core.domain.AjaxResult;
import com.bst.common.exception.base.BaseException;
import com.bst.md.service.IMdMedPubfldService;
import com.bst.md.service.IMdMedTbFldService;
import com.bst.md.service.IMdMedTbService;
import com.bst.md.service.IMdMedTblogService;
import com.bst.system.framework.datasource.DynamicDataSource;
import com.bst.system.framework.utils.DBUtil;
import com.bst.md.domain.*;
import com.bst.md.mapper.MdMedTblogItemMapper;
import com.bst.system.service.*;
import com.bst.system.vo.AlterTableParamVO;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class CommonTableDDLServiceImpl implements ICommonTableDDLService {

    private static final Logger log = LoggerFactory.getLogger(CommonTableDDLServiceImpl.class);

    @Autowired
    private IBaseDbfldCombService baseDbTpcompService;
    @Autowired
    private IMdMedTbService mdMedTbService;
    @Autowired
    private IMdMedTbFldService mdMedTbFldService;
    @Autowired
    private IMdMedTblogService mdMedTblogService;
    @Autowired
    private IMdMedPubfldService mdMedPubfldService;
    @Autowired
    MdMedTblogItemMapper mdMedTblogDtMapper;
    @Autowired
    IBaseMsgService baseMsgService;

    @Override
    public AjaxResult dropTableByMed(String cdTb, Long idTblog) {
        return this.dropTableByMedOtherDB(cdTb, idTblog, JobConstant.DB_CLS_DW);
    }

    @Override
    public AjaxResult dropTableByMedOtherDB(String cdTb,Long idTblog,String dataSourceId) {
        String sql = " TRUNCATE table "+cdTb+";DROP TABLE " + cdTb;
        MdMedTb mdMedTb = mdMedTbService.selectMdMedTbByCd(cdTb);
        if (mdMedTb == null) {
            return AjaxResult.success("表不存在");
        }
        MdMedTblog tblog;
        if (idTblog == null) {
            tblog = new MdMedTblog();
            tblog.setCdTb(cdTb);
            tblog.setDes("删除"+cdTb+"表");
            tblog.setEuStatus("error");
            mdMedTblogService.insertMdMedTblog(tblog);
        } else {
            tblog = mdMedTblogService.selectMdMedTblogByIdTblog(idTblog);
        }
        AjaxResult result = execDDLSQL(sql, dataSourceId);
        if (AjaxResult.isSuccess(result)) {
            mdMedTbService.deleteMdMedTbByIdTb(mdMedTb.getIdTb());
            mdMedTbFldService.deleteMdMedTbFldByIdTb(mdMedTb.getIdTb());
            tblog.setEuStatus("success");
        }
        mdMedTblogService.updateMdMedTblog(tblog);
        return result;
    }

    @Override
    public AjaxResult alterTableByMed(AlterTableParamVO paramVO) {
        return this.alterTableByMedOtherDB(paramVO, JobConstant.DB_CLS_DW);
    }

    @Override
    public AjaxResult alterTableByMedOtherDB(AlterTableParamVO paramVO, String dataSourceId) {
        MdMedTb newTb = paramVO.getNewTb();
        List<MdMedTbFld> newtbFldList = paramVO.getNewtbFldList();
        String oldTableName = paramVO.getOldTableName();
        MdMedTblog tblog = null;
        if (paramVO.getIdTblog() == null) {
            tblog = new MdMedTblog();
            tblog.setCdTb(newTb.getCd());
            tblog.setDes("修改表["+oldTableName+"]");
            tblog.setEuStatus("error");
            tblog.setMethod("alterTableByMed");
            tblog.setParam(JSONObject.toJSONString(paramVO));
            tblog.setEuParamtp(JobConstant.PARAM_TYPE_JSON);
            mdMedTblogService.insertMdMedTblog(tblog);
        } else {
            tblog = mdMedTblogService.selectMdMedTblogByIdTblog(paramVO.getIdTblog());
        }
        setFldLthAndPrec(newtbFldList);
        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(dataSourceId);
        String euTp = dataSource == null ? null : dataSource.getDbType();
        MdMedTb oldTb =  mdMedTbService.selectMdMedTbByCd(newTb.getCd());
        AjaxResult result;
        if (oldTb == null) { //执行表创建
            tblog.setEuTp("insert");
            result = getCreateTbSql(newTb, newtbFldList, tblog.getIdTblog(), dataSourceId);
            if ((AjaxResult.isSuccess(result))) {
                mdMedTbService.insertMdMedTb(newTb);
                for (MdMedTbFld fld : newtbFldList) {
                    fld.setIdTb(newTb.getIdTb());
                    fld.setCdTb(newTb.getCd());
                    mdMedTbFldService.insertMdMedTbFld(fld);
                }
            } else {
                endResult(result,tblog,newTb.getCd());
                return result;
            }
        } else if (oldTb != null) {
            newTb.setIdTb(oldTb.getIdTb());
            if (!StringUtils.isEmpty(oldTableName)) { //重命名表
                result = tbRenameSql(newTb.getCd(), oldTableName, tblog.getIdTblog(), dataSourceId);
                if ((AjaxResult.isSuccess(result))) {
                    newTb.setCd(newTb.getCd());
                    mdMedTbService.updateMdMedTb(newTb);
                } else {
                    endResult(result,tblog,newTb.getCd());
                    return result;
                }
            }
            for (MdMedTbFld fld : newtbFldList) {
                fld.setIdTb(newTb.getIdTb());
                fld.setCdTb(newTb.getCd());
            }
            result = getFldSql(newtbFldList, newTb, euTp, tblog.getIdTblog(), dataSourceId); //执行字段的增删改
            if(!AjaxResult.isSuccess(result)) {
                endResult(result,tblog,newTb.getCd());
                return result;
            }
        }
        result = AjaxResult.success("操作成功");
        endResult(result,tblog,newTb.getCd());
        return result;
    }

    private void endResult(AjaxResult result,MdMedTblog tblog,String tableName) {
        if (AjaxResult.isSuccess(result)) {
            tblog.setEuStatus("success");
            mdMedTblogService.updateMdMedTblog(tblog);
        }
    }

    private AjaxResult getFldSql(List<MdMedTbFld> newtbFldList, MdMedTb mdMedTb, String euTp, Long idTblog, String dataSourceId) {
        AjaxResult result;
        Boolean lastStatus = true;
        try {
            MdMedTbFld fldModel = new MdMedTbFld();
            fldModel.setIdTb(mdMedTb.getIdTb());
            List<MdMedTbFld> fldList = mdMedTbFldService.selectMdMedTbFldList(fldModel);
            Map<String, MdMedTbFld> oldtbFldList = new HashMap<>();
            for(MdMedTbFld fld:fldList) {
                oldtbFldList.put(fld.getIdPubfld(),fld);
            }
            MdMedTbFld oldTbFld;
            String idPubfld, tbFldTp;
            StringBuffer alterSql = new StringBuffer();
            Map<Integer, BaseDbfldComb> dbTpCompMap = baseDbTpcompService.findByDbTypeMap(euTp);
            Integer oldNumLth, newNumLth, oldNumPrec, newNumPrec;
            //boolean isSuccess = true;
            for (MdMedTbFld newFld : newtbFldList) {
                idPubfld = newFld.getIdPubfld();
                BaseDbfldComb baseDbTpcomp = dbTpCompMap.get(newFld.getEuJavatp());
                tbFldTp = baseDbTpcomp.getEuDbfldtp();
                String column = tbFldTp + getColumnType(newFld.getNumLth(), newFld.getNumPrec(), baseDbTpcomp.getFgPrec());

                if ((oldTbFld = oldtbFldList.get(idPubfld)) == null) { //新增
                    result = tableFldAlterSql(0, euTp, mdMedTb.getCd(), idPubfld, column, idTblog, dataSourceId);
                    if (AjaxResult.isSuccess(result)) {
                        newFld.setCdTb(mdMedTb.getCd());
                        mdMedTbFldService.insertMdMedTbFld(newFld);
                    } else {
                        return result;
                    }
                } else {
                    oldNumLth = oldTbFld.getNumLth() == null ? 0: oldTbFld.getNumLth();
                    oldNumPrec = oldTbFld.getNumPrec() == null ? 0 : oldTbFld.getNumPrec();
                    newNumPrec = newFld.getNumPrec() == null ? 0 : newFld.getNumPrec();
                    newNumLth = newFld.getNumLth() == null ? 0 : newFld.getNumLth();
                    Boolean fgUpdate=false;
                    if ((newNumLth > oldNumLth && newNumPrec >= oldNumPrec) || (newNumLth >= oldNumLth && newNumPrec > oldNumPrec)) { //长度与精度只可调大，不可调小
                        result = tableFldAlterSql(1, euTp, mdMedTb.getCd(), idPubfld, column, idTblog, dataSourceId);
                        if (AjaxResult.isSuccess(result)) {
                            fgUpdate = true;
                        } else {
                            return result;
                        }
                    }
                    if(!oldTbFld.getCdTb().equals(mdMedTb.getCd())) {
                        fgUpdate = true;
                        newFld.setCdTb(mdMedTb.getCd());
                    }
                    if(fgUpdate) {
                        mdMedTbFldService.updateMdMedTbFld(newFld);
                    }
                }
                oldtbFldList.remove(idPubfld);
            }
            for (Map.Entry<String, MdMedTbFld> entry : oldtbFldList.entrySet()) {
                result = tableFldAlterSql(2, euTp, mdMedTb.getCd(), entry.getKey(), null, idTblog, dataSourceId);
                if (AjaxResult.isSuccess(result)) {
                    mdMedTbFldService.deleteMdMedTbFldByIdTb(entry.getValue().getIdTbFld());
                } else {
                    return result;
                }
            }
        } catch (Exception e) {
            log.error("表"+mdMedTb.getCd()+"操作失败",e);
            return AjaxResult.error("表"+mdMedTb.getCd()+"操作失败");
        }
        return AjaxResult.success("创建成功");
    }

    private void setFldLthAndPrec(List<MdMedTbFld> newtbFldList) {
        List<String> idPubfldList = new ArrayList<>();
        for (MdMedTbFld fld : newtbFldList) {
            idPubfldList.add(fld.getIdPubfld());
        }
        Map<String, MdMedPubfld> pubfldList = mdMedPubfldService.findMapDataByIdList(idPubfldList);
        MdMedPubfld pubfld;
        for (MdMedTbFld fld : newtbFldList) {
            pubfld = pubfldList.get(fld.getIdPubfld());
            if (pubfld != null) {
                fld.setNumLth(pubfld.getNumLth());
                fld.setNumPrec(pubfld.getNumPrec());
                fld.setEuJavatp(pubfld.getEuJavatp());
                fld.setNaJavatp(pubfld.getNaJavatp());
                fld.setNaPubfld(pubfld.getNa());
            }
        }

    }

    private AjaxResult tableFldAlterSql(int operTp, String dbTp, String tableName, String column, String colType, Long idTblog, String dataSourceId) {
        String sql = null;
        String nameType = "";
        switch (dbTp) {
            case JobConstant.DB_TYPE_MYSQL:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD COLUMN " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " modify COLUMN " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
            case JobConstant.DB_TYPE_SQLSERVER:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD  " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " ALTER COLUMN " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
            case JobConstant.DB_TYPE_ORACLE:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " modify " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
            case JobConstant.DB_TYPE_POSTGREY:
                if (operTp == 0) { //新增
                    nameType = "新增";
                    sql = "ALTER TABLE " + tableName + " ADD  " + column + " " + colType;
                } else if (operTp == 1) { //修改
                    nameType = "修改";
                    sql = "ALTER TABLE " + tableName + " ALTER COLUMN " + column + " " + colType;
                } else if (operTp == 2) { //删除
                    nameType = "删除";
                    sql = "ALTER TABLE " + tableName + " DROP COLUMN  " + column;
                }
                break;
        }
        AjaxResult result = execDDLSQL(sql, dataSourceId);
        Boolean isSuccess = true;
        if (AjaxResult.isSuccess(result)) {
            saveDt(idTblog, "表" + tableName + nameType + "字段" + column, sql, "sucess", null);
        } else {
            saveDt(idTblog, "表" + tableName + nameType + "字段" + column, sql, "error", result.MSG_TAG);
        }
        return result;
    }

    private String getColumnType(Integer numLth, Integer numPrec, Integer euUsePrecision) {
        String type = "";
        if (euUsePrecision != 1) return type;

        if (numLth != null && numLth > 0) {
            if (numPrec != null && numPrec > 0) {
                type = "(" + numLth + "," + numPrec + ")";
            } else {
                type = "(" + numLth + ")";
            }
        }
        return type;
    }

    private AjaxResult getCreateTbSql(MdMedTb newTb, List<MdMedTbFld> newtbFldList, Long idTblog, String dataSourceId) {
        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(dataSourceId);
        Map<Integer, BaseDbfldComb> dbTpCompMap = baseDbTpcompService.findByDbTypeMap(dataSource.getDbType());
        String tableName = newTb.getCd();
        StringBuffer sb = new StringBuffer("create table " + tableName + "(");
        List<String> indexFldList = new ArrayList<>();
        for (MdMedTbFld fld : newtbFldList) {
            BaseDbfldComb baseDbTpcomp = dbTpCompMap.get(fld.getEuJavatp());
            String tbFldTp = baseDbTpcomp.getEuDbfldtp();
            if (StringUtils.isEmpty(tbFldTp)) {
                return AjaxResult.error(dataSourceId + "数据库类型中的" + fld.getEuJavatp() + "未参与对照请完善");
            }
            sb.append(fld.getIdPubfld() + " " + tbFldTp);
            if (fld.getNumLth() != null && fld.getNumLth() > 0 && baseDbTpcomp.getFgPrec() == 1) {
                if (fld.getNumPrec() != null && fld.getNumPrec() > 0) {
                    sb.append("(" + fld.getNumLth() + "," + fld.getNumPrec() + ")");
                } else {
                    sb.append("(" + fld.getNumLth() + ")");
                }
            }
            if (JobConstant.SD_CONSTP_PK.equals(fld.getSdConstp())) {
                sb.append(" primary key ");
            }
            sb.append(",");
            if(JobConstant.SD_CONSTP_INDEX.equals(fld.getSdConstp())) {
                indexFldList.add(fld.getIdPubfld());
            }
        }
        sb.append("dt_sys_cre " + dbTpCompMap.get(93).getEuDbfldtp() + ",");
        sb.append("dt_sys_modi " + dbTpCompMap.get(93).getEuDbfldtp() + ");");
        AjaxResult result = execDDLSQL(sb.toString(), dataSourceId);
        Boolean isSuccess = false;
        if (AjaxResult.isSuccess(result)) {
            for(String fld:indexFldList) {
                execDDLSQL("create index index_"+tableName+"_"+fld+" on "+newTb.getCd()+"("+fld+")", dataSourceId);
            }
            saveDt(idTblog, "表" + newTb.getNa() + "创建", sb.toString(), "success", "包含"+indexFldList.size()+"条索引");
            return AjaxResult.success(null,"表" + newTb.getNa() + "创建成功");
        } else {
            saveDt(idTblog, "表" + newTb.getNa() + "创建", sb.toString(), "error", result.MSG_TAG);
            return AjaxResult.error("表" + newTb.getNa() + "创建失败，请查看创建日志");
        }
    }

    private AjaxResult tbRenameSql(String newTableName, String oldTableName, Long idTblog, String dataSourceId) {
        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(dataSourceId);
        String euDbTp = dataSource == null ? null : dataSource.getDbType();
        String renameSql = "";
        switch (euDbTp) {
            case JobConstant.DB_TYPE_MYSQL:
                renameSql = "RENAME TABLE " + oldTableName + " TO " + newTableName + ";";
                break;
            case JobConstant.DB_TYPE_SQLSERVER:
                renameSql = "EXEC sp_rename " + oldTableName + "," + newTableName + ";";
                break;
            case JobConstant.DB_TYPE_ORACLE:
                renameSql = "RENAME " + oldTableName + " TO " + newTableName + ";";
                break;
            case JobConstant.DB_TYPE_POSTGREY:
                renameSql = "alter table " + oldTableName + " rename TO " + newTableName + ";";
                break;
        }
        if (StringUtils.isEmpty(renameSql)) {
            AjaxResult.error("数据库类型" + euDbTp + "不存在，无法执行表的重命名操作");
        }
        AjaxResult result = execDDLSQL(renameSql, dataSourceId);
        if (AjaxResult.isSuccess(result)) {
            saveDt(idTblog, "表" + oldTableName + "更名为" + newTableName, renameSql, "success", null);
            return AjaxResult.success(null,"表" + oldTableName + "创建成功");
        } else {
            saveDt(idTblog, "表" + oldTableName + "创建" + newTableName, renameSql, "error", result.MSG_TAG);
            return AjaxResult.error("表" + oldTableName + "创建失败");
        }
    }

    /**
     * 表的create、alter、drop
     *
     * @param sqls
     * @param dataSourceName
     * @throws BaseException
     */
    public AjaxResult execDDLSQL(String sqls, String dataSourceName) {
        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(dataSourceName);
        if (dataSource == null) {
            AjaxResult.error(String.format("获取目标数据库[%s]失败", dataSourceName));
        }
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            List<String> sqlList = new ArrayList<>();
            for (String sql : sqls.split(";")) {
                if (!org.springframework.util.StringUtils.isEmpty(sql) && sql.trim().length() > 6) {
                    sqlList.add(sql);
                }
            }
            Statement statement = connection.createStatement();
            for (String beforeSql : sqlList) {
                statement.execute(beforeSql);
            }
            return AjaxResult.success(null, "执行成功");
        } catch (Exception e) {
            return AjaxResult.error("执行SQL错误! 错误信息为:" + e.getMessage());
        } finally {
            DBUtil.closeDBResources(null, preparedStatement, connection);
        }
    }

    private void saveDt(Long idTblog, String title, String sql, String euStatus, String des) {
        MdMedTblogItem tblogDt = new MdMedTblogItem();
        tblogDt.setIdTblog(idTblog);
        if(title!=null && title.length()>100) {
            tblogDt.setNa(title==null?null:title.substring(0,100));
        } else {
            tblogDt.setNa(title);
        }

        tblogDt.setSqlExec(sql);
        tblogDt.setEuStatus(euStatus);
        if (des != null && des.length() > 4000) {
            des = des.substring(0, 4000);
        }
        tblogDt.setDes(des);
        mdMedTblogDtMapper.insertMdMedTblogItem(tblogDt);
    }
}